
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- 系统验证表
-- 用户，权限，结构...
----------------------------------------------------------------------- 
-- 用户表
-- 注：
-- 只有状态为0,1的用户为有效用户,当用户状态为1时,必须填写endtime字段,endtime为空或大于当前时间则不能访问系统。
create table h_auth_users(
	id 			  	  	int				not null auto_increment,	/* 编号 */
	username	   		varchar(20)		not null,					/* 用户名(账号) */
	password     		varchar(40) 	not null,               	/* 密码 */
	realname      		varchar(50) 	not null,               	/* 真实姓名 */
	roleid        		int				not null,               	/* 角色ID */
  	gender        		tinyint(1)		default 0 not null,     	/* 性别 0.男,1.女 */
  	phone		  		varchar(100)	default '-' not null,   	/* 电话 */
  	email         		varchar(100)	default '-' not null,   	/* 邮件 */
  	createtime	  		datetime 		not null,					/* 创建时间 */
  	endtime		  		datetime 		null,						/* 试用结束时间 */
	state		  		tinyint(1)		default 0 not null,			/* 状态0.正常,1.试用 2.注销 */
	primary key(id)
);
create unique index h_auth_user_u on h_auth_users(username);

-- 角色表
create table h_auth_roles(
	id					int				not null auto_increment,	/* 编号 */
	name   				varchar(50) 	not null,					/* 角色名 */
	note	   			varchar(200)	null,						/* 说明 */
	userid				int		not null,							/* 该角色的创建用户,h_auth_users.id,是由哪个用户创建的该角色*/
	primary key(id)
);

-- 权限树结构
create table h_auth_trees(
	id					int 			not null auto_increment,	/* 编号 */
	pid					int 			not null,					/* 父权限树id */
	havecid				tinyint(1) 		default 0 not null,			/* 是否有子栏目，1为有，0为无 */
	oid					int 			not null,					/* 所有栏目的排列循序 */
	alevel				int 			not null,					/* 栏目级别 */
	title				varchar(50) 	not null,					/* 标题 */
	kind				tinyint(1)		default 0 not null,			/* 节点类型，0为静态权限节点 1为动态权限节点 */ 
	note				varchar(100) 	null,						/* 备注 */
	state				tinyint(1) 		default 0 not null,			/* 状态 0.正常,1.删除,2.隐藏*/
	primary key(id)
);
create index h_a_t_oid on h_auth_trees(oid);
create index h_a_t_state_pid on h_auth_trees(state,pid);


-- 权限树节点对应的action控制器
-- 注:该节点上的权限作用到对应的action控制器上
-- 只有用户的角色为开发人员时才能维护该表
create table h_auth_actions(
	id 					int 			not null auto_increment,	/* 编号 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	actionclass			varchar(50)		not null,					/* 控制器的名字,如com.handy.permission.action.UserAction */
	primary key(id)
);
create index h_auth_action_a on h_auth_actions(actionclass);


-- 存储角色权限分配情况表
-- 注：当roleid=-1时表示所有的角色都拥有该authid的authkind权限
create table h_auth_role_infos(
	id 					int 			not null auto_increment,	/* 编号 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	authkind			varchar(10)		not null,					/* 信息维护权限,h_auth_kinds.kind */
	roleid				int				not null,					/* 角色编号,h_auth_roles.id */
	primary key(id)
);
create index h_auth_r_i_r_a on h_auth_role_infos(roleid,authid);


-- 存储用户权限分配情况表
-- 注：当userid=-1时表示所有的用户都拥有该authid的authkind权限
create table h_auth_user_infos(
	id 					int 			not null auto_increment,	/* 编号 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	authkind			varchar(10)		not null,					/* 信息维护权限,h_auth_kinds.kind */
	userid				int				not null,					/* 用户编号,h_auth_users.id */
	primary key(id)
);
create index h_auths_u_i_u_a on h_auth_user_infos(userid,authkind);

-- 动态权限分配,只有开发组成员才能操作该表
-- 注:非权限树分配的权限,例如由业务类别可能随时变动,而用户访问业务类别时是有限制的,
-- 这样权限树就不能满足需求。
-- authid在该表是唯一不可重复的，与h_auth_trees.id对应。
-- 字段名修改 condition->fcondition,修改原因：condition为mysql关键字，不能做为字段使用。
create table h_auth_dyna(
	id 					int 			not null auto_increment,	/* 编号 */
	tname				varchar(50)		not null,					/* 表名,从哪个表读数据 */
	vcol				varchar(50)		not null,					/* tname表中用来显示的字段名 */
	pcol				varchar(50)		not null,					/* tname表中用来分配权限的字段名 */
	fcondition			varchar(50)		null,						/* 检索条件 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	primary key(id)
);


-- 存储角色动态权限分配情况表
create table h_auth_dyna_role_infos(
	id 					int 			not null auto_increment,	/* 编号 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	vcolvalue			varchar(100) 	not null,					/* 显示字段的字段值 */
	pcolvalue			varchar(100)	not null,					/* 权限分配字段的字段值 */
	authkind			varchar(10)		not null,					/* 信息维护权限,h_auth_kinds.kind */
	roleid				int				not null,					/* 用户编号,h_auth_roles.id */
	primary key(id)
);
create index h_auth_d_r_i_r_p on h_auth_dyna_role_infos(roleid,pcolvalue);

 

-- 存储用户动态权限分配情况表
-- 注:该表的使用方法,例:
-- 1.取出所有的业务列表,包括用来分配权限的字段值
-- 2.与h_auth_diny_user_infos中对应tname,pcolvalue的值比较,只取存在的值。
create table h_auth_dyna_user_infos(
	id 					int 			not null auto_increment,	/* 编号 */
	authid				int 			not null,					/* 权限树编号,h_auth_trees.id */
	vcolvalue			varchar(100)	not null,					/* 显示字段的字段值 */
	pcolvalue			varchar(100)	not Null,					/* 权限分配字段的字段值 */
	authkind			varchar(10)		not null,					/* 信息维护权限,h_auth_kinds.kind */
	userid				int				not null,					/* 用户编号,h_auth_users.id */
	primary key(id)
);
create index h_auth_d_u_i_u_p on h_auth_dyna_user_infos(userid,pcolvalue);

-- 权限种类
create table h_auth_kinds(
	id 					int 			not null auto_increment,	/* 编号 */
	kind				varchar(10)     not null,					/* 权限键值 */
	name    			varchar(10)		not null,					/* 权限名称 */
	note				varchar(50)		null,						/* 权限备注 */
	primary key(id)
);

/* 系统登录，退出，权限维护日志 */
create table h_auth_logs(
	id 					int 			not null auto_increment,   	/* 编号 */
	nickname			varchar(20)		not null,					/* 用户账号 */
	recorddatetime 		datetime 		not null,					/* 访问日期 */
	note				varchar(100)	null,						/* 事件记录 */
	primary key (id)
);


-- ==================================================================
-- 视图
-- ==================================================================
-- 使用视图可直接取角色名
create view v_auth_users as 
select u.*,r.name rolename from h_auth_users u left join h_auth_roles r on u.roleid=r.id;
-- 使用实图可直接取用户名
create view v_auth_roles as 
select r.*,u.username from h_auth_roles r left join h_auth_users u on u.id=r.userid;
-- 角色权限分配情况表,左联角色名
create view v_auth_role_infos as 
select r.*,u.name name from h_auth_role_infos r left join h_auth_roles u on u.id=r.roleid;
-- 用户权限分配情况表,左联用户名
create view v_auth_user_infos as 
select r.*,u.username username,u.realname realname from h_auth_user_infos r left join h_auth_users u on u.id=r.userid;

--该视图为所有已分配权限的authid列表
create view v_auth_ids as 
  select r.authid,r.roleid,-1 userid from h_auth_role_infos r union
  select u.authid,-1 roleid,u.userid from h_auth_user_infos u union
  select dr.authid,dr.roleid,-1 userid from h_auth_dyna_role_infos dr union
  select du.authid,-1 roleid,du.userid from h_auth_dyna_user_infos du;

--该视图为所有已分配权限的authid列表(树结构)
create view v_auth_trees as select t.* , vt.* from h_auth_trees t,v_auth_ids vt where t.id=vt.authid;

-- ---------------------------------------------------------------------
-- 初始记录
-- ---------------------------------------------------------------------
-- 角色
insert into h_auth_roles(name,note,userid) values ('超级用户组','超级用户组',0);
insert into h_auth_roles(name,note,userid) values ('开发组','开发组',0);

-- 用户
insert into h_auth_users(username,password,realname,roleid,createtime) values('admin','e10adc3949ba59abbe56e057f20f883e','超级用户',1,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('dev','e10adc3949ba59abbe56e057f20f883e','程序开发人员',2,now());


insert into h_auth_trees values(1,-1,0,0,0,'权限树',0,'权限根节点,该节点不能被删除',0);
insert into h_auth_trees values(2,1,1,1,1,'权限分配',0,'是否可以操作权限分配,该节点不能被删除',0);
insert into h_auth_trees values(3,2,0,2,2,'用户管理',0,'是否充许分配用户,该节点不能被删除',0);
insert into h_auth_trees values(4,2,0,4,2,'角色管理',0,'是否充许分配角色,该节点不能被删除',0);
insert into h_auth_trees values(5,2,0,5,2,'权限分配',0,'是否充许为角色分配权限,该节点不能被删除',0);
insert into h_auth_trees values(6,2,0,6,2,'权限类别',0,'是否充许分配用户,该节点不能被删除',0);


-- 权限种类
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '1','添加','添加');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '2','删除','删除');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '3','修改','修改');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '4','查看','查看');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '6','复制','复制');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '7','审核','审核');
insert into h_auth_kinds values (h_auth_kind_seq.nextval, '8','自动审核','自动审核');




-- 测试数据
insert into h_auth_roles(name,note,userid) values ('测试用户组','测试用户组',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组1','测试用户组1',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组2','测试用户组2',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组3','测试用户组3',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组4','测试用户组4',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组5','测试用户组5',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组6','测试用户组6',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组7','测试用户组7',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组8','测试用户组8',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组9','测试用户组9',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组10','测试用户组10',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组11','测试用户组11',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组12','测试用户组12',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组13','测试用户组13',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组14','测试用户组14',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组15','测试用户组15',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组16','测试用户组16',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组17','测试用户组17',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组18','测试用户组18',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组19','测试用户组19',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组20','测试用户组20',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组21','测试用户组21',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组22','测试用户组22',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组23','测试用户组23',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组24','测试用户组24',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组25','测试用户组25',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组26','测试用户组26',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组27','测试用户组27',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组28','测试用户组28',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组29','测试用户组29',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组30','测试用户组30',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组31','测试用户组31',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组32','测试用户组32',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组33','测试用户组33',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组34','测试用户组34',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组35','测试用户组35',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组36','测试用户组36',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组37','测试用户组37',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组38','测试用户组38',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组39','测试用户组39',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组40','测试用户组40',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组41','测试用户组41',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组42','测试用户组42',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组43','测试用户组43',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组44','测试用户组44',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组45','测试用户组45',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组46','测试用户组46',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组47','测试用户组47',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组48','测试用户组48',1);
insert into h_auth_roles(name,note,userid) values ('测试用户组49','测试用户组49',1);
insert into h_auth_users(username,password,realname,roleid,createtime) values('test1','e10adc3949ba59abbe56e057f20f883e','测试用户1',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test2','e10adc3949ba59abbe56e057f20f883e','测试用户2',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test3','e10adc3949ba59abbe56e057f20f883e','测试用户3',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test4','e10adc3949ba59abbe56e057f20f883e','测试用户4',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test5','e10adc3949ba59abbe56e057f20f883e','测试用户5',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test6','e10adc3949ba59abbe56e057f20f883e','测试用户6',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test7','e10adc3949ba59abbe56e057f20f883e','测试用户7',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test8','e10adc3949ba59abbe56e057f20f883e','测试用户8',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test9','e10adc3949ba59abbe56e057f20f883e','测试用户9',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test10','e10adc3949ba59abbe56e057f20f883e','测试用户10',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test11','e10adc3949ba59abbe56e057f20f883e','测试用户11',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test12','e10adc3949ba59abbe56e057f20f883e','测试用户12',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test13','e10adc3949ba59abbe56e057f20f883e','测试用户13',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test14','e10adc3949ba59abbe56e057f20f883e','测试用户14',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test15','e10adc3949ba59abbe56e057f20f883e','测试用户15',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test16','e10adc3949ba59abbe56e057f20f883e','测试用户16',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test17','e10adc3949ba59abbe56e057f20f883e','测试用户17',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test18','e10adc3949ba59abbe56e057f20f883e','测试用户18',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test19','e10adc3949ba59abbe56e057f20f883e','测试用户19',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test20','e10adc3949ba59abbe56e057f20f883e','测试用户20',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test21','e10adc3949ba59abbe56e057f20f883e','测试用户21',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test22','e10adc3949ba59abbe56e057f20f883e','测试用户22',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test23','e10adc3949ba59abbe56e057f20f883e','测试用户23',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test24','e10adc3949ba59abbe56e057f20f883e','测试用户24',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test25','e10adc3949ba59abbe56e057f20f883e','测试用户25',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test26','e10adc3949ba59abbe56e057f20f883e','测试用户26',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test27','e10adc3949ba59abbe56e057f20f883e','测试用户27',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test28','e10adc3949ba59abbe56e057f20f883e','测试用户28',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test29','e10adc3949ba59abbe56e057f20f883e','测试用户29',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test30','e10adc3949ba59abbe56e057f20f883e','测试用户30',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test31','e10adc3949ba59abbe56e057f20f883e','测试用户31',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test32','e10adc3949ba59abbe56e057f20f883e','测试用户32',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test33','e10adc3949ba59abbe56e057f20f883e','测试用户33',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test34','e10adc3949ba59abbe56e057f20f883e','测试用户34',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test35','e10adc3949ba59abbe56e057f20f883e','测试用户35',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test36','e10adc3949ba59abbe56e057f20f883e','测试用户36',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test37','e10adc3949ba59abbe56e057f20f883e','测试用户37',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test38','e10adc3949ba59abbe56e057f20f883e','测试用户38',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test39','e10adc3949ba59abbe56e057f20f883e','测试用户39',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test40','e10adc3949ba59abbe56e057f20f883e','测试用户40',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test41','e10adc3949ba59abbe56e057f20f883e','测试用户41',3,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test42','e10adc3949ba59abbe56e057f20f883e','测试用户42',4,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test43','e10adc3949ba59abbe56e057f20f883e','测试用户43',5,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test44','e10adc3949ba59abbe56e057f20f883e','测试用户44',6,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test45','e10adc3949ba59abbe56e057f20f883e','测试用户45',7,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test46','e10adc3949ba59abbe56e057f20f883e','测试用户46',8,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test47','e10adc3949ba59abbe56e057f20f883e','测试用户47',9,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test48','e10adc3949ba59abbe56e057f20f883e','测试用户48',10,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test49','e10adc3949ba59abbe56e057f20f883e','测试用户49',11,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test50','e10adc3949ba59abbe56e057f20f883e','测试用户50',12,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test51','e10adc3949ba59abbe56e057f20f883e','测试用户51',13,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test52','e10adc3949ba59abbe56e057f20f883e','测试用户52',14,now());
insert into h_auth_users(username,password,realname,roleid,createtime) values('test53','e10adc3949ba59abbe56e057f20f883e','测试用户53',15,now());

